/*******************************************************
> Copyright (C) 2024 ==IOT== All rights reserved.
> File Name: list_to_mysql.c
> Author: lv1
> Website:www.lv1.org
> Created Time: 2024年08月02日 星期五 12时02分58秒
***************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <assert.h>
#include <string.h>
#include <mysql/mysql.h>
typedef struct stu{
	int code;
	char name[20];
	int age;
	struct stu *Next;
}STU;

//函数声明
STU* List_init();
STU* Create_list(STU *head);
void Printf_list(STU *head);
MYSQL*  connect_mysql(); 
void list_to_mysql(STU* head,MYSQL* connect);
STU* read_mysql_to_list(MYSQL* connect,STU* head);
void freeLink(STU *head);


int main(int argc, const char* argv[])
{
	//给sql中存的链表
	STU *head = NULL;
	MYSQL *connect = NULL;
	printf("头结点  初始化成功\n");
	head = List_init();
	head =Create_list(head);	
	printf("你输入的链表数据是:\n");
	Printf_list(head);	
	connect = connect_mysql();
	list_to_mysql(head,connect);
	freeLink(head);
	//新链表
	head =NULL;
	printf("新链表的头结点  初始化成功\n");
	head = List_init();	
	head = read_mysql_to_list(connect,head);	//读取完应该拿head接收新的链表
	printf("从MySQL中拿到新链表的数据：\n");
	Printf_list(head);	
	freeLink(head);

    mysql_close(connect);
	return 0;
}

STU* List_init(){
	STU *p = malloc(sizeof(STU));
	memset(p,0,sizeof(STU));
	p->Next = NULL;
	printf("\n");
	return p;
}
//新建链表
STU* Create_list(STU *head){
	assert(head != NULL);
	STU* p = head;
	STU* newnode = NULL;
	int temp = 0;
	int flag = 0;
	while(flag == 0){
	newnode = (STU*)malloc(sizeof(STU));
	memset(newnode,0,sizeof(STU));
	printf("请输入编号code:\n");
	scanf("%d",&(newnode->code));

	printf("请输入姓名name:\n");
	scanf("%s",newnode->name);

	printf("请输入年龄age:\n");
	scanf("%d",&(newnode->age));
	
	p->Next = newnode;
	newnode->Next = NULL;
	p = newnode;
	printf("继续输入吗？(1是退出):\n");
	scanf("%d",&temp);
	if(temp == 1){

			flag = 1;
		}

	}
	printf("数据插入成功！\n");
	printf("\n");
	return head;				//操作是p指针，返回必须是头结点
	
}

//打印输出的链表
void Printf_list(STU *head){
	assert(head != NULL);
	STU* p = head->Next;
	while(p != NULL){
		printf("编号：%d 姓名: %s 年龄: %d\n",p->code,p->name,p->age);
		p = p->Next;
	}
}

//连接mysql
MYSQL*  connect_mysql(){ 
	MYSQL *connect = NULL;
	MYSQL *res = NULL;
	//连接数据库
	connect = mysql_init(NULL);
    // 设置连接字符集为utf8mb4
    if (mysql_options(connect, MYSQL_SET_CHARSET_NAME, "utf8mb4") != 0) {
        fprintf(stderr, "mysql_options() failed\n");
        return NULL;
    }


	res = mysql_real_connect(connect,"localhost","username","1","cproject",0,NULL,0);
	if(res == NULL){
		printf("%s",mysql_error(connect));
		exit(-1);
	}
	printf("\n连接mysql成功！\n");
	printf("\n");
	return connect;
}
//将链表数据插入到mysql
void list_to_mysql(STU* head,MYSQL* connect){
	assert(head != NULL);
	STU* p = head->Next;
	while(p != NULL){
	char insert_mysql[100]={0};
	sprintf(insert_mysql,"insert into student values(%d,'%s',%d)",p->code,p->name,p->age);
	int ret = 0;
	ret = mysql_query(connect,insert_mysql);
	if(ret != 0)
	{
		printf("%s",mysql_error(connect));
	}
	printf("链表插入数据库成功\n");
	printf("\n");
	p = p->Next;
	}
}

//从mysql中读取数据
STU* read_mysql_to_list(MYSQL* connect,STU* head){//重新读取到新链表中，应该返回新链表头结点
	MYSQL_RES * res = NULL;//RES拿结果
	MYSQL_ROW   row =NULL;//ROW拿每一行
	STU* p = head;
	//应该是先读取！！！
	char select_sql[100]={0};
	sprintf(select_sql,"select * from student");
	//读取成功就是0
	int ret = 0;
	ret = mysql_query(connect,select_sql);
	if(ret != 0)
	{
		printf("%s",mysql_error(connect));
	}
	printf("MySQL的数据集读取成功:\n");

	MYSQL_RES * damie = NULL;//接收数据集的类型也必须是MYSQL_RES
	damie = mysql_store_result(connect);//拿到结果集，放在damie
	if(damie == NULL){
		printf("%s\n",mysql_error(connect));
		mysql_close(connect);
		exit(-1);
	}
	row = mysql_fetch_row(damie);//按行读取damie

	printf("这是从MySQL中拿出的数据\n");
	

	//--------------------获取MySQL字段名--------------------------
	int rows = 0;
	int j =0 ;
	int flag = 0; 
	MYSQL_FIELD *my_field;
	//

	while(row != NULL){

	//--------------------获取MySQL字段名---------------------------     
	if(flag==0){
	my_field = mysql_fetch_fields(damie);    
   
	for(j=0; j<3; j++)  {  
      printf("%s\t ", my_field[j].name);    
	}
	printf("\n");
	flag = 1;
	}
	//

	printf("%s\t%s\t%s",row[0],row[1],row[2]); //读取出来的都是字符串，全都是%s

	//先申请节点再连接!!!
	STU* newnode = (STU*)malloc(sizeof(STU));
	memset(newnode,0,sizeof(STU));

	sscanf(row[0],"%d",&newnode->code);//需要掌握sscanf的用法
	sscanf(row[1],"%s",newnode->name);
	sscanf(row[2],"%d",&newnode->age);
	p->Next =newnode;
	newnode->Next = NULL;
	p = newnode;
	printf("\n");
	row = mysql_fetch_row(damie);
	}
	printf("\n");
	return head;
}



//释放链表
void freeLink(STU *head)
{
	assert(head!=NULL);
	STU *temp = NULL;
	temp = head;
	STU *cur = NULL;
	while(temp!=NULL)
	{
		cur = temp->Next;
		free(temp);
		temp = NULL;
		temp = cur;
	}
}

